package org.jeecg.modules.inspect.service.impl;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.logging.log4j.util.Strings;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.system.vo.DictModel;
import org.jeecg.common.util.UUIDGenerator;
import org.jeecg.modules.inspect.entity.InsDeleteBackup;
import org.jeecg.modules.inspect.mapper.InsDeleteBackupMapper;
import org.jeecg.modules.inspect.service.IInsDeleteBackupService;
import org.jeecg.modules.system.service.ISysDictService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.*;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description: 班次管理
 * @Author: jeecg-boot
 * @Date:   2024-03-10
 * @Version: V1.0
 */
@Service
public class InsDeleteBackupServiceImpl extends ServiceImpl<InsDeleteBackupMapper, InsDeleteBackup> implements IInsDeleteBackupService {

	@Autowired
	private InsDeleteBackupMapper insDeleteBackupMapper;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private ISysDictService sysDictService;

	@Value("${deleteBackupDataParam.backupFilePath}")
	private String backupFilePath;//备份文件储存位置
	@Value("${deleteBackupDataParam.delCycle}")
	private String delCycle;//备份周期 单位：天
	@Value("${deleteBackupDataParam.delBackupKey}")
	private String delBackupKey;//删除数据功能模块key 多个逗号分隔

	@Value("${deleteBackupDataParam.jdbcName}")
	private String jdbcName;
	@Value("${deleteBackupDataParam.jdbcPassword}")
	private String jdbcPassword;
	@Value("${deleteBackupDataParam.jdbcIP}")
	private String jdbcIP;
	@Value("${deleteBackupDataParam.jdbcDdatabase}")
	private String jdbcDdatabase;
	//表数据恢复
	@Override
	public Result<String> backupRecover(String id) {
		InsDeleteBackup insDeleteBackup = insDeleteBackupMapper.selectById(id);
		if(insDeleteBackup==null){
			return Result.error("数据备份失败 该数据错误!");
		}

		String backupUrl = backupFilePath+insDeleteBackup.getBackupUrl();
		File file = new File(backupUrl);
		if(!file.exists()){
			return Result.error("数据备份失败 备份文件不存在!");
		}

		try {
			// 构建命令
			String command = "cmd /c mysql" +
					" -u" + jdbcName +
					" -p" + jdbcPassword +
					" -h" + jdbcIP +
					" " + jdbcDdatabase +
					" < " + backupUrl;
			long startTime = System.currentTimeMillis();
			System.out.println("开始恢复【"+insDeleteBackup.getBackupName()+"】备份数据");
			// 执行命令
			Process process = Runtime.getRuntime().exec(command);
			process.waitFor();
			BufferedReader br = new BufferedReader(new InputStreamReader(process.getInputStream()));
			String line;
			while ((line = br.readLine()) != null){
				log.debug(line);
				System.out.println(line);
			}
			long endTime = System.currentTimeMillis();
			long executionTime = endTime - startTime;
			System.out.println("Exit Value: " + process.exitValue());
			System.out.println("恢复备份【"+insDeleteBackup.getBackupName()+"】数据结束 用时："+executionTime/1000+"秒");

		} catch (Exception e) {
			e.printStackTrace();
		}
		return Result.error("数据备份成功!");
	}

	@Override
	public void backupTable(String tableKey) {
		if(StringUtils.isBlank(tableKey)){
			throw new RuntimeException("同步表名为空！");
		}
		Integer delCycleDay = 0;
		if(StringUtils.isNotBlank(delCycle)){
			delCycleDay = Integer.parseInt(delCycle);
			delCycleDay = delCycleDay<30?30:delCycleDay;
		}
//		List<String> delBackupKeyList = new ArrayList<>();
//		if(StringUtils.isNotBlank(delBackupKey)){
//			String[] delBackupKeySplit = delBackupKey.split(",");
//			delBackupKeyList = Arrays.asList(delBackupKeySplit);
//		}

		//获取列名
		Map<String,List<String>> tableColumnMap = new HashMap<>();
		//获取数据
		Map<String, List<Map<String, Object>>> tableDatas = new HashMap<>();
		if(StringUtils.isNotBlank(delBackupKey)){
			tableColumnMap = this.getTableColumns(delBackupKey);
			tableDatas = this.getTableDatas(delBackupKey, delCycleDay);
		}else{
			tableColumnMap = this.getTableColumns(tableKey);
			tableDatas = this.getTableDatas(tableKey, delCycleDay);
		}

		if(tableDatas.isEmpty()){
			throw new RuntimeException("暂无可同步数据！");
		}

		//拼接sql
		for (String tabKey : tableDatas.keySet()) {
			StringBuffer sql = new StringBuffer();
			long startTime = System.currentTimeMillis();
			System.out.println("开始备份"+tabKey+"数据");
			List<Object[]> delIds = new ArrayList<>();
			//获取数据
			List<Map<String, Object>> datas = tableDatas.get(tabKey);
			if(CollectionUtils.isEmpty(datas))  continue;
			//获取列名
			List<String> tableColumns = tableColumnMap.get(tabKey);
			if(CollectionUtils.isEmpty(datas))  continue;
			//当前表全部数据
			for (Map<String, Object> data : datas) {
				Object id = data.get("id") != null ? data.get("id") : data.get("ID");
				if(delIds.contains(id)){
					continue;
				}else{
					delIds.add(new Object[]{id});
				}
				List<Object> keys = new ArrayList<>();
				List<Object> vals = new ArrayList<>();
				//循环列名
				for (String columnKey : tableColumns) {
					Object val = data.get(columnKey);
					if(val!=null && !"".equals(val)){
						keys.add(columnKey);
						if( val instanceof String || val instanceof Date ||
							val instanceof LocalDate || val instanceof LocalDateTime ||
							val instanceof LocalTime
						){
							vals.add("'"+val+"'");
						}else{
							vals.add(val);
						}
					}
				}
				sql.append(" INSERT INTO ");
				sql.append( tabKey );
				sql.append( " ( " );
				sql.append( Strings.join(keys, ',') );
				sql.append( " ) VALUES (" );
				sql.append( Strings.join(vals, ',') );
				sql.append( " ) " );
				sql.append(";");
				sql.append("\r\n");
			}
			String tabName = "备份数据";
			List<DictModel> backup_sql_type = sysDictService.queryDictItemsByCode("backup_sql_type");
			if(CollectionUtils.isNotEmpty(backup_sql_type)){
				List<DictModel> collect = backup_sql_type.stream().filter(o -> o.getValue().equals(tabKey)).collect(Collectors.toList());
				if(CollectionUtils.isNotEmpty(collect)){
					tabName = collect.get(0).getText()+tabName;
				}
			}else{
				tabName = tableKey+tabName;
			}
			Date now = new Date();
			String fileName = tabName+
					new SimpleDateFormat("yyyy-MM-dd_HHmmss").format(now);
			String backupName = tabName+
					new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now);
			try{
				//将sql写入磁盘
				String backupFilePath = this.writeFile(sql.toString(), fileName);
				String[] fileReturnSplit = backupFilePath.split("%&@@&%");

				InsDeleteBackup insDeleteBackup = new InsDeleteBackup();
				insDeleteBackup.setId(fileReturnSplit[1]);
				insDeleteBackup.setBackupUrl(fileReturnSplit[0]);
				insDeleteBackup.setBackupName(backupName);
				insDeleteBackup.setBackupType(tabKey);
				insDeleteBackup.setCreateBy("admin");
				insDeleteBackup.setCreateTime(now);
				insDeleteBackup.setUpdateBy("admin");
				insDeleteBackup.setUpdateTime(now);
				insDeleteBackupMapper.insert(insDeleteBackup);
				//删除数据
				jdbcTemplate.batchUpdate("delete from "+tabKey+" where id=?",delIds);
			}catch (Exception e){
				e.printStackTrace();
			}
			long endTime = System.currentTimeMillis();
			long executionTime = endTime - startTime;
			System.out.println("备份"+tabKey+"数据结束 用时："+executionTime/1000+"秒");
		}
	}

	//根据表名获取表数据
	public Map<String, List<Map<String,Object>>> getTableDatas(String tableName, Integer dayNum) {
		String[] tableNames = tableName.split(",");

		Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();

		for (String tabName : tableNames) {
			//异常消息表 包含三张表ins_classes_unusual_msg、ins_classes_unusual_msg_course、ins_classes_unusual_msg_issue
			if("ins_classes_unusual_msg".equals(tabName)){
				List<Map<String, Object>> msgList = jdbcTemplate.queryForList("SELECT * FROM ins_classes_unusual_msg WHERE CREATE_TIME <= DATE_SUB(CURDATE(), INTERVAL " + dayNum + " DAY)");
				if(CollectionUtils.isNotEmpty(msgList)){
					List<Map<String, Object>> courseList = jdbcTemplate.queryForList(
							"select c.* from ins_classes_unusual_msg_course c where " +
									"c.unusual_id in ( SELECT m.id FROM ins_classes_unusual_msg m WHERE m.CREATE_TIME <= DATE_SUB(CURDATE(), INTERVAL " + dayNum + " DAY) )");
					List<Map<String, Object>> issueList = jdbcTemplate.queryForList(
							"select c.* from ins_classes_unusual_msg_issue c where " +
									"c.unusual_id in ( SELECT m.id FROM ins_classes_unusual_msg m WHERE m.CREATE_TIME <= DATE_SUB(CURDATE(), INTERVAL " + dayNum + " DAY) )");
					result.put("ins_classes_unusual_msg",msgList);
					result.put("ins_classes_unusual_msg_course",courseList);
					result.put("ins_classes_unusual_msg_issue",issueList);
				}

			}//巡检记录表 ins_task_record
			else {
				List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT * FROM "+tabName+" WHERE CREATE_TIME <= DATE_SUB(CURDATE(), INTERVAL " + dayNum + " DAY)");
				result.put(tabName,list);
			}
		}
		return result;
	}
	//根据表名获取表字段
	public Map<String,List<String>> getTableColumns(String tableName) {
		Map<String, List<String>> result = new HashMap<String, List<String>>();
		String[] split = tableName.split(",");
		for (String tabKey : split) {
			List<String> columnNames = jdbcTemplate.execute((ConnectionCallback<List<String>>) (connection) -> {
				DatabaseMetaData metaData = connection.getMetaData();
				ResultSet resultSet = metaData.getColumns(null, null, tabKey, null);
				List<String> columns = new ArrayList<>();
				while (resultSet.next()) {
					columns.add(resultSet.getString("COLUMN_NAME"));
				}
				return columns;
			});
			result.put(tabKey, columnNames);
		}
		return result;
	}

	//将文件写入磁盘
	public String writeFile(String sql,String fileName) throws IOException {
		String fileId = UUIDGenerator.generate();
		String backUrl = File.separator + "backupFile" + File.separator + fileId + File.separator + fileName+".sql";
		String folderPath = backupFilePath+File.separator + "backupFile" + File.separator + fileId;
		File folderFile = new File(folderPath);
		if(!folderFile.exists()){
			FileUtils.forceMkdir(folderFile);
		}
		String filePath = folderPath+File.separator+fileName+".sql";// 文件路径

		BufferedWriter writer = null;
		try {
			writer = new BufferedWriter(new FileWriter(filePath));
			writer.write(sql);
			writer.newLine(); // 添加换行符
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			if(writer!=null){
				try {
					writer.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}

		return backUrl+"%&@@&%"+fileId;
	}
}
